package org.posupe.cursos;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.posupe.bdr.PersistenceMechanismException;
import org.posupe.bdr.PersistenceMechanismRDBMS;
import org.posupe.instituicoesEnsino.InstituicaoEnsinoInexistenteException;
import org.posupe.instituicoesEnsino.RepositorioInstituicoesEnsinoBDR;
import org.posupe.util.RepositorioException;

public class RepositorioCursosBDR implements RepositorioCursos {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private static RepositorioCursosBDR instance;
	private PersistenceMechanismRDBMS pm;

	private RepositorioCursosBDR() throws RepositorioException {
		try {
			pm = PersistenceMechanismRDBMS.getInstance();
			pm.connect();
		}
		catch (Exception ex) {
			throw new RepositorioException(ex);
		}
	}

	public synchronized static RepositorioCursosBDR getInstance() throws RepositorioException {
		if (instance == null) {
			instance = new RepositorioCursosBDR();

		}
		return instance;
	}

	public void inserir(Curso curso) throws RepositorioException {
		try {
			String sql = "INSERT INTO CURSO(CODIGO, NOME, DESCRICAO, NIVEL, COD_INSTITUICAO, CARGA_HORARIA) VALUES (?,?,?,?,?,?)";
			PreparedStatement statement = (PreparedStatement) pm.getCommunicationChannel(sql);
			statement.setInt(1, curso.getCodigo());
			statement.setString(2, curso.getNome());
			statement.setString(3, curso.getDescricao());
			statement.setString(4, curso.getNivel());
			statement.setInt(5, curso.getInstituicaoEnsino().getCodigo());
			statement.setInt(6, curso.getCargaHoraria());
			statement.executeUpdate();
			
			statement.close();
		} catch(PersistenceMechanismException e){
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
	}

	public void remover(int codigo) throws CursoInexistenteException, RepositorioException {
		try{
			String sql = "DELETE FROM CURSO WHERE CODIGO = ? ";
			PreparedStatement statement = (PreparedStatement) pm.getCommunicationChannel(sql);
			statement.setInt(1, codigo);
			int i = statement.executeUpdate();
			if (i == 0) {
				throw new CursoInexistenteException(codigo);
			}
			
			statement.close();
		} catch(PersistenceMechanismException e){
			throw new RepositorioException(e);
		} catch(SQLException e){
			throw new RepositorioException(e);            
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
	}

	public Curso procurar(int codigo) throws CursoInexistenteException, RepositorioException {
		Curso curso = null;
		try {
			String sql = "SELECT CODIGO, NOME, DESCRICAO, NIVEL, CARGA_HORARIA, COD_INSTITUICAO FROM CURSO WHERE CODIGO = ? ";
			PreparedStatement statement = (PreparedStatement) pm.getCommunicationChannel(sql);
			statement.setInt(1, codigo);
			ResultSet resultset = statement.executeQuery();
			if (resultset.next()) {
				curso = new Curso(resultset.getInt("CODIGO"),
						resultset.getString("NOME"),
						resultset.getString("DESCRICAO"),
						resultset.getString("NIVEL"),
						RepositorioInstituicoesEnsinoBDR.getInstance().procurar(resultset.getInt("COD_INSTITUICAO")),
						resultset.getInt("CARGA_HORARIA"));
			} else {
				throw new CursoInexistenteException(codigo);
			}
			
			statement.close();
			resultset.close();
		} catch(PersistenceMechanismException e){
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		} catch (InstituicaoEnsinoInexistenteException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
		return curso;
	}

	public void atualizar(Curso curso) throws CursoInexistenteException, RepositorioException {
		try {
			String sql = "UPDATE CURSO SET NOME = ?, DESCRICAO = ?, NIVEL = ?, COD_INSTITUICAO = ?, CARGA_HORARIA = ? " + 
			"WHERE CODIGO = ? ";
			PreparedStatement statement =  (PreparedStatement)pm.getCommunicationChannel(sql);
			statement.setString(1, curso.getNome());
			statement.setString(2, curso.getDescricao());
			statement.setString(3, curso.getNivel());
			statement.setInt(4, curso.getInstituicaoEnsino().getCodigo());
			statement.setInt(5, curso.getCargaHoraria());
			statement.setInt(6, curso.getCodigo());
			int i = statement.executeUpdate();
			if (i == 0) {
				throw new CursoInexistenteException(curso.getCodigo());
			}
			
			statement.close();
		} catch(PersistenceMechanismException e){
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
	}

	public boolean existe(int codigo) throws RepositorioException {
		boolean resposta = false;
		try {

			String sql = "SELECT 1 FROM CURSO WHERE CODIGO = ? ";
			PreparedStatement statement = (PreparedStatement) pm.getCommunicationChannel(sql);
			statement.setInt(1, codigo);
			ResultSet resultset = statement.executeQuery();
			resposta = resultset.next();
			
			statement.close();
			resultset.close();
		} catch(PersistenceMechanismException e){
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
		return resposta;
	}


	public IteratorCursos getIterator() throws RepositorioException {
		IteratorCursosVector resultado = new IteratorCursosVector();
		try {
			String sql = "SELECT CODIGO, NOME, DESCRICAO, NIVEL, CARGA_HORARIA, COD_INSTITUICAO FROM CURSO";
			Statement statement = (Statement) pm.getCommunicationChannel();
			ResultSet resultset = statement.executeQuery(sql);
			Curso curso = null;
			while (resultset.next()) {
				curso = new Curso(resultset.getInt("CODIGO"),
						resultset.getString("NOME"),
						resultset.getString("DESCRICAO"),
						resultset.getString("NIVEL"),
						RepositorioInstituicoesEnsinoBDR.getInstance().procurar(resultset.getInt("COD_INSTITUICAO")),
						resultset.getInt("CARGA_HORARIA"));
				resultado.add(curso);
			}
			
			statement.close();
			resultset.close();
		} catch(PersistenceMechanismException e){
			throw new RepositorioException(e);
		} catch (SQLException e) {
			throw new RepositorioException(e);
		} catch (InstituicaoEnsinoInexistenteException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
		return resultado;
	}

	public IteratorCursos procurar(Curso curso) throws RepositorioException {
		IteratorCursos cursos = this.getIterator();
		IteratorCursosVector resultado = new IteratorCursosVector();
		while(cursos.hasNext()){
			Curso cursoAtual = cursos.next();
			if (cursoAtual.match(curso)){
				resultado.add(cursoAtual);
			}
		}
		return resultado;
	}
}